package pm.workgroup.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pm.db.DBUtil;
import pm.user.bean.UserBean;
import pm.workgroup.bean.WorkgroupBean;

public class WorkgroupService {

	
	public Boolean addWorkgroup(WorkgroupBean workgroup) {
		boolean flag=false;
		WorkgroupBean workgroupBean = new WorkgroupBean();
		String sql = "insert into pm_group(workgroupname,workgroupmember) values("
				+ "'"
				+ workgroup.getWorkgroupName()
				+ "'"
				+ ","
				+ "'"
				+ workgroup.getWorkgroupMember() + "'" + ")";
		Connection conn = DBUtil.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate();
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}

		return flag;
	}
	
	public Boolean updateGroup(WorkgroupBean workgroup) {
		boolean flag=false;
		WorkgroupBean workgroupBean = new WorkgroupBean();
		String sql = "update  pm_group set workgroupmember="
				+"'"+workgroup.getWorkgroupMember()+"'"+
				"where workgroupname="+"'"+workgroup.getWorkgroupName()+"'";
		Connection conn = DBUtil.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate();
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}

		return flag;
	}
	
	public List showAllGroup(){
		List<WorkgroupBean> list = new ArrayList<WorkgroupBean>();

		String sql1 = "select * from pm_group";

		PreparedStatement past = null;
		Connection conn = DBUtil.getConnection();
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);

			rs = past.executeQuery();
			while (rs.next()) {
				WorkgroupBean group = new WorkgroupBean();

				group.setWorkgroupName(rs.getString("workgroupname"));
				group.setWorkgroupMember(rs.getString("workgroupmember"));
				
				list.add(group);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		return list;
	}
	
	public List showAllGroupExcept(String groupname){
		List<WorkgroupBean> list = new ArrayList<WorkgroupBean>();

		String sql1 = "select * from pm_group";

		PreparedStatement past = null;
		Connection conn = DBUtil.getConnection();
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);

			rs = past.executeQuery();
			while (rs.next()) {
				WorkgroupBean group = new WorkgroupBean();

				group.setWorkgroupName(rs.getString("workgroupname"));
				group.setWorkgroupMember(rs.getString("workgroupmember"));
				if(!group.getWorkgroupName().equals(groupname)){
				list.add(group);
				}
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		return list;
	}
	
	public boolean delectGroup(String workgroupname){

		 boolean flag = false;
		 Connection conn = DBUtil.getConnection();
		 String sql = "delete from pm_group where workgroupname='"
		 + workgroupname + "'";
		 PreparedStatement past = null;
		 try {
		 past = conn.prepareStatement(sql);
		 past.executeUpdate();
		 past.close();
		 conn.close();
		 flag = true;
		 } catch (SQLException e) {
		 e.printStackTrace();
		 } finally {
			 DBUtil.close(conn);
		 }
		 return flag;
		 
	}
	
}
